﻿
SET QUOTED_IDENTIFIER ON
GO

SET ANSI_NULLS ON
GO

IF EXISTS (SELECT 1 FROM sys.objects WHERE [object_id] = OBJECT_ID(N'[dbo].[fn_can_access_by_role]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
    DROP FUNCTION [dbo].[fn_can_access_by_role];
GO
CREATE FUNCTION [dbo].[fn_can_access_by_role]
(
    @Class_ID   int,    -- the id of the class to validate (UDS_Class.ClassID)
    @UserID     int,    -- the name of the user
    @Act_ID     int     -- the id of the permission (UDS_Proc.Proc_ID)
)
RETURNS BIT
AS
BEGIN

--此用户的角色能否直接访问全局类(0)？
IF EXISTS(
    SELECT 1 
        FROM 
            dbo.UDS_Role a,
            dbo.UDS_Staff_In_Role b,        
            dbo.UDS_Assign_Rule e
        WHERE
            a.Role_ID = b.Role_ID
            and b.staff_id = @UserID
            and e.based_on = 3 /* role */
            and e.role_id = a.role_id 
            and e.act_id = @act_id
            and e.team_id = 0  /* 0 indicates global class */
)
BEGIN
    RETURN 1;
END

--以下判断是判断用户是否再任何一个能访问此类的父类的角色中(能访问父类就能访问子类)
--得到所有父组中拥有此权利的角色ID
DECLARE @pAssignedRole TABLE (RoleID int not null);
INSERT INTO @pAssignedRole (RoleID)
    SELECT Role_ID 
        FROM 
            dbo.UDS_Assign_Rule A
            INNER JOIN dbo.UDS_Class B ON A.Team_ID = B.ClassID
        WHERE
            A.based_on = 3 /* role */
            AND A.act_id = @Act_id
            AND B.ClassID IN (
                SELECT ClassID FROM dbo.fn_get_parent_class(@Class_ID)
            );

--用户是否在有权利的角色ID中?
IF EXISTS (
    SELECT 1
    FROM 
        dbo.UDS_Staff_in_Role b
        INNER JOIN @pAssignedRole c ON b.Role_ID = c.RoleID
    WHERE
        b.staff_id = @UserID
)
BEGIN
    RETURN 1;
END

-- Access denied.
RETURN 0;
END
GO